package com.roc.service.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.roc.newPage.IPageDao;
import com.roc.pojo.WindControllSta;
import com.roc.service.ServiceException;
import com.roc.service.WindControllStaService;
import com.roc.util.Constance;
@Service
public class WindControllStaServiceImpl implements WindControllStaService{
	private Logger logger=LogManager.getLogger(WindControllStaServiceImpl.class);
	@Autowired
	private IPageDao pageDao;
	
	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public List<WindControllSta> queryWindControllStaList(List<Object> params,
			int startRow, int maxRow) throws ServiceException {
		String sql = "select a.accountNo,a.realName,a.total,b.passCount,b.noPassCount from ("+
				"SELECT COUNT(R.OP_ID) total, S.ACCOUNT_NO accountNo,s.real_name realName "+
				"FROM T_RECEIVE R, T_OPERATORS S "+
				"WHERE S.ACCOUNT_NO = R.OP_ACCOUNTNO "+
						"AND SUBSTR(r.createdate, 1, 10) >= ? "+
						"AND SUBSTR(r.createdate, 1, 10) <= ? "+
				"GROUP BY S.ACCOUNT_NO,s.real_name"+
				") a left join "+ 
				"(SELECT sum(case when I.RESULT_CODE = '01' then 1 else 0 end) passCount,"+ 
				        "sum(case when I.RESULT_CODE = '02' then  1  else  0 end) noPassCount,"+
				        "s.account_no accountNo "+
				"FROM T_AUDITING_ITEM I, T_OPERATORS S "+
				"WHERE I.OPERATORS = S.ACCOUNT_NO "+
					"AND TO_CHAR(I.DT_CREATE, 'yyyy-MM-dd') >= ? "+
					"AND TO_CHAR(I.DT_CREATE, 'yyyy-MM-dd') <= ? "+
				"GROUP BY S.ACCOUNT_NO,s.real_name) "+
				"b	on a.accountNo = b.accountNo order by nvl(a.total,0) desc,nvl(b.passCount,0) desc";
		logger.info("查询sql:"+sql);
		List<Object> searchRsList=new ArrayList<Object>();
		List<WindControllSta> wcsList= new ArrayList<WindControllSta>();
		try {
			searchRsList = pageDao.findAllByParams(sql, params, startRow, maxRow);
			logger.info("查询到记录："+searchRsList.size()+"条");
			wcsList = dealTheListSpecially(wcsList,searchRsList);
		} catch (Exception e) {
			logger.info("查询风控统计数据异常"+e);
		}
		return wcsList;
	}
	
	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public int queryWindControllStaCount(List<Object> params) throws ServiceException{
		String sql = "select count(1) from ("+
				"SELECT COUNT(R.OP_ID) total, S.ACCOUNT_NO accountNo,s.real_name realName "+
				"FROM T_RECEIVE R, T_OPERATORS S "+
				"WHERE S.ACCOUNT_NO = R.OP_ACCOUNTNO "+
						"AND SUBSTR(r.createdate, 1, 10) >= ? "+
						"AND SUBSTR(r.createdate, 1, 10) <= ? "+
				"GROUP BY S.ACCOUNT_NO,s.real_name"+
				") a left join "+ 
				"(SELECT sum(case when I.RESULT_CODE = '01' then 1 else 0 end) passCount,"+ 
				        "sum(case when I.RESULT_CODE = '02' then  1  else  0 end) noPassCount,"+
				        "s.account_no accountNo "+
				"FROM T_AUDITING_ITEM I, T_OPERATORS S "+
				"WHERE I.OPERATORS = S.ACCOUNT_NO "+
						"AND TO_CHAR(I.DT_CREATE, 'yyyy-MM-dd') >= ? "+
						"AND TO_CHAR(I.DT_CREATE, 'yyyy-MM-dd') <= ? "+
				"GROUP BY S.ACCOUNT_NO,s.real_name) "+
				"b	on a.accountNo = b.accountNo";
		logger.info("风控数据数量查询sql："+sql);
		int rowCount = 0;
		try {
			rowCount = pageDao.countByParams(sql,params);
			logger.info("风控数据数量查询sql结果："+rowCount);
		} catch (Exception e) {
			logger.info("风控数据数量查询异常"+e);
		}
		return rowCount;
	}
	
	
	public List<WindControllSta> dealTheListSpecially(List<WindControllSta> wcsList,List<Object> searchRsList){
		if(searchRsList != null && searchRsList.size()>0){
			Iterator it = searchRsList.iterator();
			while(it.hasNext()){
				Object[] objArray = (Object[]) it.next();
				WindControllSta wcs = new WindControllSta();
				wcs.setAccountNo(Constance.Null2EmptyTrim(objArray[0]));
				wcs.setRealName(Constance.Null2EmptyTrim(objArray[1]));
				wcs.setTotal(Constance.null2Zero(objArray[2]));
				wcs.setPassCount(Constance.null2Zero(objArray[3]));
				wcs.setNoPassCount(Constance.null2Zero(objArray[4]));
				wcsList.add(wcs);
			}
			
		}
		return wcsList;
	}
	

}
